﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Zj.CourseManage.Domain.Core;
using Zj.CourseManage.DTO.Common;
using Zj.CourseManage.DTO.Common.Pagination;
using System.Data.SqlClient;

namespace Zj.CourseManage.Mvc.Controllers
{
    public class OfficesController : Controller
    {
        private DbContext _ctx;
        public OfficesController(DbContext ctx)
        {
            this._ctx = ctx;
        }
        // GET: Offices
        public ActionResult Index(int pageIndex = 1, int pageSize = 3, string sortField = "", string searString = "")
        {
            var entities = _ctx.Set<Office>().AsQueryable();
            ViewData["pageIndex"] = pageIndex;

            ViewData["sortName"] = sortField.Equals("") ? "nameDesc" : "";
            ViewData["sortId"] = sortField.Equals("idDesc") ? "id" : "idDesc";
            ViewData["searString"] = searString;
            if (!string.IsNullOrEmpty(searString))
            {
                entities = entities.Where(o => o.Name.Contains(searString));
            }
            switch (sortField)
            {
                case "nameDesc":
                    entities = entities.OrderByDescending(o => o.Name);
                    break;
                case "idDesc":
                    entities = entities.OrderByDescending(o => o.Id);
                    break;
                case "id":
                    entities = entities.OrderBy(o => o.Id);
                    break;
                default:
                    entities = entities.OrderBy(o => o.Name);
                    break;
            }
            var dtos = entities.Select(o => new OfficeDTO() { Id = o.Id, Name = o.Name });
            var list = PaginatedList<OfficeDTO>.CreatePaging(dtos, pageIndex, pageSize);
            return View(list);
        }

        // GET: Offices/Details/5
        public ActionResult Details(int id)
        {
            return View();
        }

        // GET: Offices/Create
        public ActionResult Create()
        {
            return View();
        }

        // POST: Offices/Create
        [HttpPost]
        [ValidateAntiForgeryToken]
        public async Task<ActionResult> Create(OfficeDTO dto)
        {
            try
            {
                var entity = new Office() { Name = dto.Name };
                _ctx.Add(entity);
                // TODO: Add insert logic here
                await _ctx.SaveChangesAsync();
                return RedirectToAction(nameof(Index));
            }
            catch
            {
                return View();
            }
        }

        // GET: Offices/Edit/5
        public async Task<ActionResult> Edit(int? id)
        {
            if (id == null)
            {
                return NotFound();
            }
            var entity = await _ctx.Set<Office>().AsNoTracking().SingleOrDefaultAsync(o => o.Id == id);
            if (entity == null)
            {
                return NotFound();
            }
            var dto = new OfficeDTO() { Id = entity.Id, Name = entity.Name };
            return View(dto);
        }

        // POST: Offices/Edit/5
        [HttpPost]
        [ValidateAntiForgeryToken]
        public async Task<ActionResult> Edit(OfficeDTO dto)
        {
            try
            {
                if (ModelState.IsValid)
                {
                    var entity = new Office() { Id = dto.Id, Name = dto.Name };
                    await TestEFSql(dto);
                    _ctx.Update(entity);
                    await _ctx.SaveChangesAsync();
                    return RedirectToAction(nameof(Index));
                }
                else
                {
                    ModelState.AddModelError("", "实体验证失败，请检查重试");
                    return View();
                }
                // TODO: Add update logic here
            }
            catch
            {
                return View();
            }
        }



        /// <summary>
        /// test entity framework sql
        /// </summary>
        private async Task TestEFSql(OfficeDTO dto)
        {
            try
            {
                var test = _ctx.Set<Office>().FromSql("select * from [Office] where Id=@Id", new SqlParameter("@Id", dto.Id)).AsNoTracking().SingleOrDefault();

                //var officeList = new List<OfficeDTO>();
                //using (var myConn = _ctx.Database.GetDbConnection())
                //{
                //    await myConn.OpenAsync();
                //    using (var cmd = myConn.CreateCommand())
                //    {
                //        cmd.CommandText = "select * from [Office] where Id=@Id";
                //        cmd.Parameters.Add(new SqlParameter("@Id", dto.Id));
                //        var dr = cmd.ExecuteReader();
                //        while (await dr.ReadAsync())
                //        {
                //            var id = dr.GetInt32(0);
                //            var name = dr.GetString(1);
                //            officeList.Add(new OfficeDTO() { Id = id, Name = name });
                //        }
                //    }
                //}
                //var testQuery = officeList.SingleOrDefault();

                var count = _ctx.Database.ExecuteSqlCommand(
                     "update [Office] set Name=@Name where Id=@Id",
                     new SqlParameter("@Name", "张总办公室"),
                     new SqlParameter("@Id", dto.Id));
                test = _ctx.Set<Office>().FromSql("select * from [Office] where Id=@Id", new SqlParameter("@Id", dto.Id)).AsNoTracking().SingleOrDefault();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message); 
            }
        }

        public async Task<ActionResult> Delete(int? id)
        {
            try
            {
                if (id == null)
                {
                    return NotFound();
                }
                var entity = await _ctx.Set<Office>().SingleOrDefaultAsync(o => o.Id == id);
                // TODO: Add update logic here
                if (entity == null)
                {
                    return NotFound();
                }
                _ctx.Remove(entity);
                await _ctx.SaveChangesAsync();
                return RedirectToAction(nameof(Index));
            }
            catch
            {
                return View();
            }
        }



        public async Task<ActionResult> ModalEdit(int? id)
        {

            if (id == null)
            {
                return NotFound();
            }
            var entity = await _ctx.Set<Office>().AsNoTracking().SingleOrDefaultAsync(o => o.Id == id);
            if (entity == null)
            {
                return NotFound();
            }
            var dto = new OfficeDTO() { Id = entity.Id, Name = entity.Name };
            return View(dto);
        }
    }
}